﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace DoeObjects
{
    /// <summary>
    /// class for the category of a loanable
    /// </summary>
    public class LoanableCategory
    {
        #region attributes
        /// <summary>
        /// the id of the category
        /// </summary>
        public int CategoryId { get; set; }

        /// <summary>
        /// the name of the category
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// the id of the user, which created (--> owns) this category
        /// </summary>
        public User Owner { get; set; }

        #endregion

        #region static methods

        /// <summary>
        /// returns a category
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static LoanableCategory getById(int id)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand("SELECT * FROM LoanableCategory WHERE LoanableCategoryId = @id", connection);
            command.Parameters.AddWithValue("@id", id);

            LoanableCategory cat = null;
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    cat = getCurrentLoanableCategory(reader, connection);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return cat;
        }

        /// <summary>
        /// returns all loanablecategories
        /// </summary>
        /// <returns></returns>
        public static List<LoanableCategory> getAll()
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand("SELECT * FROM LoanableCategory", connection);

            List<LoanableCategory> categories = new List<LoanableCategory>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    categories.Add(getCurrentLoanableCategory(reader, connection));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return categories;
        }

        /// <summary>
        /// returns all names of loanablecategories
        /// </summary>
        /// <returns></returns>
        public static List<string> getAllNamesDistinct(string like = null)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand("SELECT DISTINCT LoanableCategoryName FROM LoanableCategory WHERE LoanableCategoryName LIKE @name", connection);

            if (like != null)
            {
                like = like.Replace(' ', '%');
                like = "%" + like + "%";
            }
            else
            {
                like = "%";
            }

            SqlParameter nameParam = new SqlParameter("@name", SqlDbType.VarChar);
            nameParam.Value = like;
            command.Parameters.Add(nameParam);

            List<string> categories = new List<string>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    categories.Add(reader["LoanableCategoryName"] as string);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return categories;
        }


        /// <summary>
        /// returns the category with the given name which is owned by the given user. Returns null if the user is not owner of a category with the given name
        /// </summary>
        /// <param name="name"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static LoanableCategory getByName(string name, int userId)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand("SELECT * FROM LoanableCategory WHERE LoanableCategoryName = @name AND LoanableCategoryOwner = @userId", connection);
            
            SqlParameter nameParam = new SqlParameter("@name", SqlDbType.VarChar);
            nameParam.Value = name;
            command.Parameters.AddWithValue("@userId", userId);
            command.Parameters.Add(nameParam);

            LoanableCategory cat = null;
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    cat = getCurrentLoanableCategory(reader, connection);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return cat;
        }

        /// <summary>
        /// returns all loanable categories which matches to the given title
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static List<LoanableCategory> getByName(string name)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand("SELECT * FROM LoanableCategory WHERE LOWER(LoanableCategoryName) LIKE @name", connection);

            SqlParameter nameParam = new SqlParameter("@name", SqlDbType.VarChar);
            nameParam.Value = "%" + name.Replace(' ', '%').ToLower() + "%";
            command.Parameters.Add(nameParam);

            List<LoanableCategory> cats = new List<LoanableCategory>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    cats.Add(getCurrentLoanableCategory(reader, connection));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return cats;
        }

        /// <summary>
        /// adds a new loancategory to the database and returns the new Id of the loan. Returns 0 if the insert failed
        /// </summary>
        /// <param name="loan"></param>
        /// <returns>Last inserted ID</returns>
        public static int add(LoanableCategory cat) 
        {
            int lastInsertedId = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("INSERT INTO loanableCategory (LoanableCategoryName, LoanableCategoryOwner) OUTPUT INSERTED.loanableCategoryId VALUES (@Name, @UserId)", connection);
            cmd.Parameters.AddWithValue("@UserId", cat.Owner.UserId);
            
            SqlParameter nameParameter = new SqlParameter("@Name", SqlDbType.VarChar);
            nameParameter.Value = cat.Name;
            cmd.Parameters.Add(nameParameter);

            try
            {
                connection.Open();
                Object res = cmd.ExecuteScalar();
                if (res != null)
                {
                    lastInsertedId = (int)res;
                }
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return lastInsertedId;
        }

        /// <summary>
        /// updates a loanablecategory in the database
        /// </summary>
        /// <param name="cat"></param>
        /// <returns></returns>
        public static bool update(LoanableCategory cat)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("UPDATE loanableCategory SET LoanableCategoryName = @Name, LoanableCategoryOwner = @UserId WHERE loanableCategoryId = @Id", connection);
            cmd.Parameters.AddWithValue("@Id", cat.CategoryId);
            cmd.Parameters.AddWithValue("@UserId", cat.Owner.UserId);
            SqlParameter nameParameter = new SqlParameter("@Name", SqlDbType.VarChar);
            nameParameter.Value = cat.Name;
            cmd.Parameters.Add(nameParameter);

            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

        /// <summary>
        /// creates a new categoryobject based on data from the datareader
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        internal static LoanableCategory getCurrentLoanableCategory(SqlDataReader reader, SqlConnection connection)
        {
            LoanableCategory cat = null;
            if (reader != null)
            {
                cat = new LoanableCategory();
                cat.CategoryId = (int)reader["LoanableCategoryId"];
                cat.Name = reader["LoanableCategoryName"] as string;
                cat.Owner = User.getUserByUserId((int)reader["LoanableCategoryOwner"], connection); //(int)reader["UserId"];
            }

            return cat;
        }
        public static List<LoanableCategory> getAllFromUser(int userId)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand("SELECT * FROM LoanableCategory WHERE loanableCategoryOwner = @UserId", connection);
            command.Parameters.AddWithValue("@UserId", userId);

            List<LoanableCategory> cats = new List<LoanableCategory>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    cats.Add(getCurrentLoanableCategory(reader, connection));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return cats;
        }
        #endregion

        #region objectmethods

        /// <summary>
        /// saves the current object to the database and updates the Id of the object if an insert was executed
        /// </summary>
        /// <returns></returns>
        public bool save()
        {
            if (this.CategoryId == 0)
            {
                this.CategoryId = LoanableCategory.add(this); //set the created id
                return (this.CategoryId != 0);
            }
            else
            {
                return LoanableCategory.update(this);
            }
        }

        #endregion


        
    }
}
